﻿Imports Proyecto_PAVI_Academia.AccesoBD

Public Class frm_ABM_Alumnos

    Enum estado        
        insertar
        modificar
        ver
        eliminar
    End Enum

    Dim _accion As estado = estado.insertar
    Dim _accesoBD As New AccesoBD
    Dim _legajo As Integer

    Private Sub btn_salir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_salir.Click
        Me.Close()
    End Sub

    Private Sub frm_ABM_Alumnos_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing
        If MessageBox.Show("Esta seguro que desea salir?!", "Importante", MessageBoxButtons.YesNo, MessageBoxIcon.Question) _
            = Windows.Forms.DialogResult.No Then
            e.Cancel = True
        Else
            e.Cancel = False
        End If
    End Sub

    Private Sub frm_ABM_Alumnos_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load    
        'Me.panel_datos.Enabled = False
        'Me.btn_grabar.Enabled = False
        _accion = estado.ver
        comportamientoBotones()
        cargar_combos()
        cargar_grilla()
        Me.CmbProvinciaLocalidad1.inicializar()
    End Sub

    Private Sub cargar_combos()
        _accesoBD.load_combo(Me.cmb_tipo_doc, _accesoBD.devuelveTabla("TipoDocumento"), "idTipoDoc", "descripcion")

        _accesoBD.load_combo(Me.cmb_localidad, _accesoBD.devuelveTabla("Localidades"), "idLocalidad", "localidad")
    End Sub

    Private Sub cargar_grilla()
        Dim tabla As New DataTable
        Dim consulta As String = ""

        consulta = "select legajoAlumno,nombre,apellido,nroDoc from Alumnos where estado = 0"

        tabla = _accesoBD.ejecutaSQL(consulta)

        Me.grid_alumnos.Rows.Clear()

        Dim c As Integer = 0

        For c = 0 To tabla.Rows.Count - 1
            Me.grid_alumnos.Rows.Add(tabla.Rows(c)("legajoAlumno"), tabla.Rows(c)("nombre"), tabla.Rows(c)("apellido"), tabla.Rows(c)("nroDoc"))
        Next
    End Sub

    Private Function validar() As Boolean
        If Me.txt_nombre.Text = "" Then
            MessageBox.Show("Debe ingresar Nombre", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.txt_nombre.Focus()
            Return False
            Exit Function
        End If

        If Me.txt_apellido.Text = "" Then
            MessageBox.Show("Debe ingresar Apellido", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.txt_apellido.Focus()
            Return False
            Exit Function
        End If

        If Me.txt_documento.Text = "" Then
            MessageBox.Show("Debe ingresar Documento", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.txt_documento.Focus()
            Return False
            Exit Function
        End If

        If cmb_tipo_doc.SelectedValue = -1 Then
            MessageBox.Show("Debe seleciconar un Tipo de Documento", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.cmb_tipo_doc.Focus()
            Return False
            Exit Function
        End If

        If Me.txt_calle.Text = "" Then
            MessageBox.Show("Debe ingresar Calle", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.txt_calle.Focus()
            Return False
            Exit Function
        End If

        If Me.txt_numero.Text = "" Then
            MessageBox.Show("Debe ingresar Numero", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.txt_numero.Focus()
            Return False
            Exit Function
        End If

        If Me.txt_CP.Text = "" Then
            MessageBox.Show("Debe ingresar CP", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.txt_CP.Focus()
            Return False
            Exit Function
        End If

        If cmb_localidad.SelectedValue = -1 Then
            MessageBox.Show("Debe seleciconar una Localidad", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.cmb_localidad.Focus()
            Return False
            Exit Function
        End If

        Return True
    End Function

    Private Sub btn_nuevo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_nuevo.Click        
        Me.limpiar_controles()
        _accion = estado.insertar
        comportamientoBotones()
        'Me.panel_datos.Enabled = True
        'Me.btn_grabar.Enabled = True
        Me.txt_nombre.Focus()
        Me.txt_fecha_ingreso.Text = Date.Now
    End Sub

    Private Sub insertar()
        Dim consulta, depto, piso As String
        'Dim piso As Integer

        If String.IsNullOrWhiteSpace(Me.txt_piso.Text) Then
            piso = ""
        Else
            piso = Me.txt_piso.Text
        End If

        If String.IsNullOrWhiteSpace(Me.txt_depto.Text) Then
            depto = ""
        Else
            depto = Me.txt_depto.Text
        End If

        consulta = "insert into Alumnos(nombre,apellido,idTipoDoc,nroDoc,calle,numero,piso,depto,cp,idLocalidad,fechaIngreso)"
        consulta &= " values('" & Me.txt_nombre.Text & "'"
        consulta &= ", '" & Me.txt_apellido.Text & "'"
        consulta &= ", " & Me.cmb_tipo_doc.SelectedValue
        consulta &= ", " & Me.txt_documento.Text
        consulta &= ", '" & Me.txt_calle.Text & "'"
        consulta &= ", '" & Me.txt_numero.Text & "'"
        consulta &= ", '" & piso & "'"
        consulta &= ", '" & depto & "'"
        consulta &= ", '" & Me.txt_CP.Text & "'"
        consulta &= ", '" & Me.cmb_localidad.SelectedValue & "'"
        consulta &= ", '" & Convert.ToDateTime(Me.txt_fecha_ingreso.Text).Date & "')"

        _accesoBD._insertar(consulta)
    End Sub

    Private Sub btn_grabar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_grabar.Click
        If validar() Then
            If _accion = estado.insertar Then
                If Me.validar_existente Then
                    Me.insertar()                    
                Else
                    MessageBox.Show("Ya existe un alumno cargado con ese documento", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Exit Sub
                End If
            ElseIf _accion = estado.modificar Then
                Me.modificar(_legajo)
            End If
            Me.cargar_grilla()
            Me.limpiar_controles()
            Me.panel_datos.Enabled = False
            Me.btn_grabar.Enabled = False
            MessageBox.Show("Se grabo exitosamente!", "Informacion", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
    End Sub

    Private Sub limpiar_controles()
        'Dim objeto As Control
        Dim miControl As Object

        'For Each objeto In Me.panel_busqueda.Controls
        '    If TypeOf objeto Is TextBox Then objeto.Text = ""
        '    If TypeOf objeto Is MaskedTextBox Then objeto.Text = ""
        'Next

        'limpia elementos del group box
        For Each miControl In Me.panel_datos.Controls
            If miControl.GetType Is GetType(System.Windows.Forms.TextBox) Then
                miControl.Clear()
            End If
            If miControl.GetType Is GetType(System.Windows.Forms.ComboBox) Then
                miControl.SelectedValue = -1
            End If
            If miControl.GetType Is GetType(System.Windows.Forms.MaskedTextBox) Then
                miControl.Clear()
            End If
            System.Windows.Forms.Application.DoEvents()
        Next

        'Me.cmb_tipo_doc.SelectedValue = -1
        'Me.cmb_localidad.SelectedValue = -1
        Me.txt_nombre.Focus()

    End Sub

    Private Sub txt_documento_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txt_documento.KeyPress, txt_numero.KeyPress, _
        txt_piso.KeyPress, txt_CP.KeyPress

        Select Case Asc(e.KeyChar)
            Case 4, 24, 19, 127, 13, 9, 15, 14, 8
                Exit Sub
        End Select
        If IsNumeric(e.KeyChar) = False Then
            MessageBox.Show("Este caracter no es un número ( " + e.KeyChar + " )", "Importante", MessageBoxButtons.OK, MessageBoxIcon.Error)
            e.KeyChar = ""
        End If
    End Sub

    Private Sub txt_fecha_ingreso_Validated(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txt_fecha_ingreso.Validated
        If sender.text = "  /  /" Then
            Exit Sub
        End If

        If IsDate(sender.text) Then
            If Not sender.text = Format(CDate(sender.text), "dd/mm/yyyy") Then
            Else
                MessageBox.Show("Fecha Invalida", "Atencion", MessageBoxButtons.OK, MessageBoxIcon.Error)
                sender.Focus()
                Exit Sub
            End If
        Else
            MessageBox.Show("Fecha invalida", "Atencion", MessageBoxButtons.OK, MessageBoxIcon.Error)
            sender.Focus()
            Exit Sub
        End If
    End Sub

    Private Function validar_existente() As Boolean
        Dim consulta As String
        Dim tabla As New DataTable

        consulta = "select * from Alumnos where idTipoDoc = " & Me.cmb_tipo_doc.SelectedValue & " and nroDoc= " & Me.txt_documento.Text

        tabla = _accesoBD.ejecutaSQL(consulta)

        If tabla.Rows.Count = 1 Then
            Return False
        Else
            Return True
        End If        
    End Function

    Private Sub btn_cancelar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_cancelar.Click
        Me.limpiar_controles()       
    End Sub

    Private Sub cargar_grilla_busqueda()
        Dim consulta As String
        Dim tabla As New DataTable

        consulta = "select legajoAlumno,nombre,apellido,nroDoc from Alumnos where estado = 0"
        consulta &= " and nombre like '" & Me.txt_nombre_busca.Text & "'"
        consulta &= " or apellido like '" & Me.txt_apellido_busca.Text & "'"

        tabla = _accesoBD.ejecutaSQL(consulta)

        Me.grid_alumnos.Rows.Clear()

        Dim c As Integer = 0

        For c = 0 To tabla.Rows.Count - 1
            Me.grid_alumnos.Rows.Add(tabla.Rows(c)("legajoAlumno"), tabla.Rows(c)("nombre"), tabla.Rows(c)("apellido"), tabla.Rows(c)("nroDoc"))
        Next
    End Sub

    Private Sub btn_buscar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_buscar.Click
        If String.IsNullOrWhiteSpace(Me.txt_nombre_busca.Text) And String.IsNullOrWhiteSpace(Me.txt_apellido_busca.Text) Then
            Me.cargar_grilla()
        Else
            Me.cargar_grilla_busqueda()
        End If
    End Sub

    Private Sub grid_alumnos_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grid_alumnos.CellDoubleClick
        Dim doc As String = Me.grid_alumnos.CurrentRow.Cells("col_documento").Value
        _legajo = Me.grid_alumnos.CurrentRow.Cells("col_legajo").Value
        Dim consulta As String
        Dim tabla As New DataTable

        consulta = "select * from Alumnos where nroDoc = " & doc
        tabla = _accesoBD.ejecutaSQL(consulta)

        Me.txt_nombre.Text = tabla.Rows(0)("nombre")
        Me.txt_apellido.Text = tabla.Rows(0)("apellido")
        Me.cmb_tipo_doc.SelectedValue = tabla.Rows(0)("idTipoDoc")
        Me.txt_documento.Text = tabla.Rows(0)("nroDoc")
        Me.txt_calle.Text = tabla.Rows(0)("calle")
        Me.txt_numero.Text = tabla.Rows(0)("numero")
        Me.txt_piso.Text = tabla.Rows(0)("piso")
        Me.txt_depto.Text = tabla.Rows(0)("depto")
        Me.txt_CP.Text = tabla.Rows(0)("cp")
        Me.cmb_localidad.SelectedValue = tabla.Rows(0)("idLocalidad")
        Me.txt_fecha_ingreso.Text = tabla.Rows(0)("fechaIngreso")        
        _accion = estado.ver
        comportamientoBotones()
        'Me.panel_datos.Enabled = False
        'Me.btn_grabar.Enabled = False
    End Sub

    Private Sub btn_refrescar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_refrescar.Click
        Me.cargar_grilla()
        Me.txt_nombre_busca.Text = ""
        Me.txt_apellido_busca.Text = ""
    End Sub

    Private Sub modificar(ByVal legajo As Integer)
        Dim consulta As String

        consulta = "Update Alumnos set nombre = '" & Me.txt_nombre.Text & "'"
        consulta &= ", apellido = '" & Me.txt_apellido.Text & "'"
        consulta &= ", idTipoDoc = " & Me.cmb_tipo_doc.SelectedValue
        consulta &= ", nroDoc = " & Me.txt_documento.Text
        consulta &= ", calle = '" & Me.txt_calle.Text & "'"
        consulta &= ", numero = " & Me.txt_numero.Text
        consulta &= ", piso = '" & Me.txt_piso.Text & "'"
        consulta &= ", depto = '" & Me.txt_depto.Text & "'"
        consulta &= ", cp = " & Me.txt_CP.Text
        consulta &= ", idLocalidad = " & Me.cmb_localidad.SelectedValue
        consulta &= ", fechaIngreso = '" & Me.txt_fecha_ingreso.Text & "'"
        consulta &= " where legajoAlumno = " & legajo

        _accesoBD._modificar(consulta)
    End Sub
    Private Sub eliminar(ByVal legajo As Integer)
        Dim consulta As String = "Update Alumnos set estado = 1 where legajoAlumno = " & legajo
        If Not Me.txt_documento.Text = "" Then
            _accesoBD._modificar(consulta)
        End If
    End Sub    
    Private Sub ModificarToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ModificarToolStripMenuItem.Click                        
        _accion = estado.modificar
        comportamientoBotones()
        'Me.panel_datos.Enabled = True
        'Me.btn_grabar.Enabled = True
    End Sub

    Private Sub EliminarToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EliminarToolStripMenuItem.Click
        Dim legajo = Me.grid_alumnos.CurrentRow.Cells("col_legajo").Value
        If MessageBox.Show("Esta seguro que desea eliminar el registro?!", "Eliminar", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            Me.eliminar(legajo)
            Me.cargar_grilla()
            Me.limpiar_controles()
            _accion = estado.eliminar
            comportamientoBotones()
            'Me.panel_datos.Enabled = False
            'Me.btn_grabar.Enabled = False
        End If
    End Sub

    Private Sub comportamientoBotones()
        If _accion = estado.insertar Or _accion = estado.modificar Then
            Me.panel_datos.Enabled = True
            Me.btn_grabar.Enabled = True
        Else
            Me.panel_datos.Enabled = False
            Me.btn_grabar.Enabled = False
        End If
    End Sub

    Private Sub grid_alumnos_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grid_alumnos.CellClick
        Dim doc As String = Me.grid_alumnos.CurrentRow.Cells("col_documento").Value
        _legajo = Me.grid_alumnos.CurrentRow.Cells("col_legajo").Value
        Dim consulta As String
        Dim tabla As New DataTable

        consulta = "select * from Alumnos where nroDoc = " & doc
        tabla = _accesoBD.ejecutaSQL(consulta)

        Me.txt_nombre.Text = tabla.Rows(0)("nombre")
        Me.txt_apellido.Text = tabla.Rows(0)("apellido")
        Me.cmb_tipo_doc.SelectedValue = tabla.Rows(0)("idTipoDoc")
        Me.txt_documento.Text = tabla.Rows(0)("nroDoc")
        Me.txt_calle.Text = tabla.Rows(0)("calle")
        Me.txt_numero.Text = tabla.Rows(0)("numero")
        Me.txt_piso.Text = tabla.Rows(0)("piso")
        Me.txt_depto.Text = tabla.Rows(0)("depto")
        Me.txt_CP.Text = tabla.Rows(0)("cp")
        Me.cmb_localidad.SelectedValue = tabla.Rows(0)("idLocalidad")
        Me.txt_fecha_ingreso.Text = tabla.Rows(0)("fechaIngreso")
        _accion = estado.ver
        comportamientoBotones()
    End Sub
End Class